package com.ctg.behavior.calc.tools;

import com.ctg.behavior.common.calc.CtCalcMeasure;
import com.ctg.behavior.common.consts.CtAggregatorConsts;

import org.apache.commons.lang3.StringUtils;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Administrator
 * @date 2021/6/7 10:00
 */
public class AggregatorToSqlTool {

    public Map<String,String> repAggToSql (CtCalcMeasure measure,Date datetime){
        Map<String,String> map = new HashMap<>();
        StringBuffer fieldStr = new StringBuffer();
        StringBuffer groupStr = new StringBuffer();
        StringBuffer connStr = new StringBuffer();
        if(measure !=null){
             String eventId = measure.getEventId();
             String aggregator = measure.getAggregator();
             if(StringUtils.isNotBlank(aggregator)){

                 /**
                  * unique	用户数
                  * general	总次数
                  * average	人均次数
                  * count_last_day_7	过去7天总次数
                  * uniq_last_day_7	过去7天用户数
                  * count_last_day_30	过去30天总次数
                  * uniq_last_day_30	过去30天用户数
                  * count_cur_month	当月总次数
                  * uniq_cur_month	当月用户数
                  * rollup_count	合计总次数
                  * rollup_uniq	合计用户数
                  * 
                  * SUM	总和
                  * AVG	均值
                  * MAX	最大值
                  * MIN	最小值
                  * uniqAvg	人均值
                  * uniqCount	去重数
                  */

                 if(CtAggregatorConsts.KEY_GENERAL.equals(aggregator)){
                     fieldStr.append("count(*)");
                    if(StringUtils.isNotBlank(eventId))
                         connStr.append(" event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_UNIQUE.equals(aggregator)){
                     fieldStr.append("count(user_id)");
                     groupStr.append("group by user_id");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_AVERAGE.equals(aggregator)){

                 }
                 if(CtAggregatorConsts.KEY_COUNT_LAST_DAY_7.equals(aggregator)){
                     fieldStr.append("count(*)");
                     connStr.append(" event_date >='").append(this.getBeforDays(7,datetime)).append("'");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" and event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_UNIQ_LAST_DAY_7.equals(aggregator)){
                     fieldStr.append("count(user_id)");
                     groupStr.append("group by user_id");
                     connStr.append(" event_date >='").append(this.getBeforDays(7,datetime)).append("'");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append("and event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_COUNT_LAST_DAY_30.equals(aggregator)){
                     fieldStr.append("count(*)");
                     connStr.append(" event_date >='").append(this.getBeforDays(30,datetime)).append("'");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" and event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_UNIQ_LAST_DAY_30.equals(aggregator)){
                     fieldStr.append("count(user_id)");
                     groupStr.append("group by user_id");
                     connStr.append(" event_date >='").append(this.getBeforDays(30,datetime)).append("'");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append("and event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_COUNT_CUR_MONTH.equals(aggregator)){
                     fieldStr.append("count(*)");
                     connStr.append(" event_date like '").append(this.getMonth(0,datetime)).append("%'");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" and event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_UNIQ_CUR_MONTH.equals(aggregator)){
                     fieldStr.append("count(user_id)");
                     groupStr.append("group by user_id");
                     connStr.append(" event_date  like '").append(this.getMonth(0,datetime)).append("%'");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append("and event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_ROLLUP_COUNT.equals(aggregator)){

                 }
                 if(CtAggregatorConsts.KEY_ROLLUP_UNIQ.equals(aggregator)){

                 }
                 if(CtAggregatorConsts.KEY_SUM.equals(aggregator)){
                     String field = measure.getField();
                     String[] fields = field.split("\\.");
                     fieldStr.append("SUM(").append(fields[2]).append(")");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" event = '").append(eventId).append("'");

                 }
                 if(CtAggregatorConsts.KEY_AVG.equals(aggregator)){
                     String field = measure.getField();
                     String[] fields = field.split("\\.");
                     fieldStr.append("AVG(").append(fields[2]).append(")");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_MAX.equals(aggregator)){
                     String field = measure.getField();
                     String[] fields = field.split("\\.");
                     fieldStr.append("MAX(").append(fields[3]).append(")");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_MIN.equals(aggregator)){
                     String field = measure.getField();
                     String[] fields = field.split("\\.");
                     fieldStr.append("MIN(").append(fields[2]).append(")");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" event = '").append(eventId).append("'");
                 }
                 if(CtAggregatorConsts.KEY_UNIQAVG.equals(aggregator)){

                 }
                 if(CtAggregatorConsts.KEY_UNIQCOUNT.equals(aggregator)){
                     String field = measure.getField();
                     String[] fields = field.split("\\.");
                     fieldStr.append("COUNT(DISTINCT ").append(fields[2]).append(")");
                     if(StringUtils.isNotBlank(eventId))
                         connStr.append(" event = '").append(eventId).append("'");
                 }
             }
        }
        map.put("fields",fieldStr.toString());
        map.put("conn",connStr.toString());
        map.put("group",groupStr.toString());
        return map;
    }

    /**
     * 向前推days的日期yyyyMMdd
     * @param days
     * @return
     */
    public String getBeforDays(int days,Date datetime){
        SimpleDateFormat sdf =  new SimpleDateFormat("yyyyMMdd");
        if(datetime == null)
          datetime = new Date();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(datetime);
        calendar.add(Calendar.DATE, -days);
        Date dateTm = calendar.getTime();
        System.out.println("向前推"+days+"时间="+sdf.format(dateTm));
        return sdf.format(dateTm);
    }

    /**
     * 获取yyyymm格式月份
     * @param months
     * @return
     */
    public String getMonth(int months,Date datetime){
        SimpleDateFormat sdf =  new SimpleDateFormat("yyyyMM");
        if(datetime == null)
            datetime = new Date();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(datetime);
        calendar.add(Calendar.MONTH, -months);
        Date dateTm = calendar.getTime();
        System.out.println("向前推"+months+"时间="+sdf.format(dateTm));
        return sdf.format(dateTm);
    }


    public static void main(String[] args) {
        AggregatorToSqlTool tools = new AggregatorToSqlTool();
        tools.getBeforDays(7,new Date());
        tools.getMonth(0,new Date());
    }
}
